﻿using System.Collections.Generic;
using System.Data.Objects;

namespace NorthwindEntities
{
    class GetAllCustomersByOrderDate
    {
        public static HashSet<Customer> GetAllCustomers() 
        {
            HashSet<Customer> customers = new HashSet<Customer>();
            NorthwindEntities context = new NorthwindEntities();

            foreach (var cust in context.Customers.Include("Orders")) 
            {
                foreach (var order in cust.Orders) 
                {
                    ProcesOrder(order, cust, customers);
                }
            }
            
            return customers;
        }
  
        private static void ProcesOrder(Order order, Customer cust, HashSet<Customer> customers)
        {
            if (order.ShippedDate != null)
            {
                if (order.ShipCountry == "Canada" && order.ShippedDate.Value.Year == 1997)
                {
                    if (!customers.Contains(cust))
                    {
                        customers.Add(cust);
                    }
                }
            }
        }

        public static IEnumerable<IdAndContactName> GetAllCustomersQuerry() 
        {
            NorthwindEntities context = new NorthwindEntities();
            string nativeQuerry = "Select cust.CustomerID, cust.ContactName " +
                                    " from Customers cust join Orders ord " +
                                    " on cust.CustomerID = ord.CustomerID " +
                                    " Where ord.ShipCountry = 'Canada' and ord.ShippedDate >= '1997-01-01' " +
                                    " and ord.ShippedDate <= '1998-01-01' " +
                                    " group by cust.CustomerID, cust.ContactName";
            ObjectResult<IdAndContactName> cust = context.ExecuteStoreQuery<IdAndContactName>(nativeQuerry);

            return cust;
        }
    }
}
